

***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


***************************************
*** BASE DATA 
***************************************

tempfile temp_base0
save  `temp_base0' , replace

*drop observations with emtpy prices 
count if askprice_can==. & bidprice_can!=.

keep if askprice_can!=.
count if bidprice_can==.


*round to x decimals
foreach var of varlist bidprice_can askprice_can {
	gen double a1=`var'*100000
	gen double a0 = round(a1, 1)
	replace `var' = a0 /100000
	drop a1 a0
}

tempfile temp_base
save  `temp_base' , replace


*********************************
*** EXCEL SHEET WITH YAS YIELDS
*********************************

foreach y of numlist 2019 2018 2017 2016  { 
	foreach i of numlist 1/2{

	import excel "`dir'/2 YAS/all_CD_`y'.xlsx", sheet("`y'_`i'") firstrow clear

	destring bidYAS_can, replace force 
	destring askYAS_can, replace force 
		
	tempfile temp_`y'_`i'
	save temp_`y'_`i' , replace
	
	}
}


import excel "`dir'/2 YAS/all_CD_2020.xlsx", sheet("2020_1") firstrow clear

	destring bidYAS_can, replace force 
	destring askYAS_can, replace force 

	tempfile temp_2020_1
	save temp_2020_1 , replace

	foreach y of numlist  2016 2017 2018 2019 { //2018 2019
		foreach i of numlist 1/2{
			append using temp_`y'_`i'
		}
	}

	
*destring and rename  
keep isin settlement_date bidprice_can askprice_can bidYAS_can askYAS_can 

*dop duplicates
duplicates drop 


***************************************
*** MERGE WITH BASE DATA AND CLEAN UP
***************************************

*merge with base data 		
merge 1:m isin settlement_date bidprice_can askprice_can  using `temp_base'

replace bidYAS_can=bidYAS_can*100
replace askYAS_can=askYAS_can*100

*some do not match because of funny rounding when going between software programs. We add them via an id ``manually'
tab _merge

drop if _merge==1 // those are observations in YAS that are not in my data
rename _merge  _merge_round1_can
count

tempfile time_final
save  `time_final' , replace
	
	
*bring back the observations that I droped above	
use  `temp_base0' , clear 
keep if askprice_can==. | bidprice_can ==.
append using `time_final'
count 




